# -*- coding: utf-8 -*-
# =============================================================================
#     FileName:
#         Desc:
#       Author: GGA
#        Email:
#     HomePage:
#      Version: 1.0.1
#   LastChange: 2020-12-20
#      History:
# =============================================================================
import os
import sys

import fire
import datetime
import logging
import time
from logging.handlers import RotatingFileHandler
from utils.mysql_helper import MySQLClient
from utils.logger_helper import LoggerHelper

DEFAULT_DATETIME_FORMAT = "%m-%d %H:%M:%S:%f"

logger = LoggerHelper.get_logger()


class MySQLQpsMonitor(object):
    CUMULATE_STATUS_SET = {
        'QUESTIONS',
        'COM_SELECT',
        'COM_INSERT',
        'COM_DELETE',
        'COM_UPDATE',
        'COM_COMMIT',
        'COM_ROLLBACK',
        'INNODB_ROWS_READ',
        'INNODB_ROWS_INSERTED',
        'INNODB_ROWS_DELETED',
        'INNODB_ROWS_UPDATED',
        'INNODB_BUFFER_POOL_READ_REQUESTS',
        'INNODB_BUFFER_POOL_WRITE_REQUESTS',
        'INNODB_BUFFER_POOL_READS',
        'INNODB_BUFFER_POOL_READ_AHEAD',
        'INNODB_DATA_READS',
        'INNODB_DATA_WRITES',
    }
    RAW_STATUS_SET = {
        'THREADS_CONNECTED'
    }
    FULL_STATUS_SET = RAW_STATUS_SET | CUMULATE_STATUS_SET

    def __init__(self, mysql_host, mysql_port,
                 mysql_user, mysql_pass,
                 check_interval=1, check_count=10000):
        self.mysql_host = mysql_host
        self.mysql_port = mysql_port
        self.mysql_user = mysql_user
        self.mysql_pass = mysql_pass
        self.check_interval = check_interval
        if check_count < 0:
            self.check_count = 999999999999
        else:
            self.check_count = check_count
        self._loop_counter = 0
        self._mysql_client = self._get_mysql_client()
        self._last_status_info = None
        logger_name = "{}_{}_QPS".format(
            str(self.mysql_host).replace(".", "_").replace("-", "_"),
            self.mysql_port
        )
        self.monitor_logger = self._get_logger(logger_name=logger_name)

    @classmethod
    def _get_logger(cls, logger_name):
        curr_logger = logging.getLogger(logger_name)
        curr_logger.setLevel(logging.DEBUG)
        base_dir = os.path.dirname(__file__)
        log_dir = os.path.join(base_dir, "logs")
        if not os.path.exists(log_dir):
            os.makedirs(log_dir)
        log_formatter = logging.Formatter('%(message)s')
        log_file_name = "{}.log".format(logger_name)
        log_file_path = os.path.join(log_dir, log_file_name)
        file_handler = RotatingFileHandler(log_file_path, maxBytes=50 * 1024 * 1024, backupCount=5)
        file_handler.setFormatter(log_formatter)
        curr_logger.addHandler(file_handler)
        stream_handler = logging.StreamHandler(stream=sys.stdout)
        stream_handler.setFormatter(log_formatter)
        curr_logger.addHandler(stream_handler)
        return curr_logger

    def _get_mysql_client(self):
        server_config = {
            "mysql_host": self.mysql_host,
            "mysql_port": self.mysql_port,
            "mysql_user": self.mysql_user,
            "mysql_password": self.mysql_pass,
            "database_name": "mysql"
        }
        return MySQLClient(**server_config)

    def _check_server_conn(self):
        if not self._mysql_client.test_connection():
            logger.info("目标数据库无法正常连接")
            sys.exit(-1)
        pass

    def _get_raw_status_info(self):
        info_dict = {"CHECK_TIME": datetime.datetime.now()}
        sql_script = """
SELECT VARIABLE_NAME,VARIABLE_VALUE
FROM information_schema.GLOBAL_STATUS 
WHERE VARIABLE_NAME IN ( {status_items}
);
"""
        status_items = ["'" + item + "'" for item in self.FULL_STATUS_SET]
        sql_script = sql_script.format(status_items=",".join(status_items))
        query_rows = self._mysql_client.mysql_query(sql_script=sql_script, return_dict=True)
        for query_row in query_rows:
            info_dict[query_row["VARIABLE_NAME"]] = query_row["VARIABLE_VALUE"]
        return info_dict

    def _get_real_status_info(self, ):
        real_status_info = dict()
        curr_status_info = self._get_raw_status_info()
        base_status_info = self._last_status_info
        self._last_status_info = curr_status_info
        if base_status_info is None:
            return None
        status_interval = (curr_status_info["CHECK_TIME"] - base_status_info["CHECK_TIME"]).total_seconds()
        for statue_name in self.CUMULATE_STATUS_SET:
            diff_value = int(curr_status_info[statue_name]) - int(base_status_info[statue_name])
            statue_value = int(diff_value / status_interval)
            real_status_info[statue_name] = statue_value
        for statue_name in self.RAW_STATUS_SET:
            real_status_info[statue_name] = int(curr_status_info[statue_name])
        real_status_info["CHECK_TIME"] = curr_status_info["CHECK_TIME"]
        logic_read = int(real_status_info["INNODB_BUFFER_POOL_READ_REQUESTS"])
        physical_read1 = int(real_status_info["INNODB_BUFFER_POOL_READS"])
        physical_read2 = int(real_status_info["INNODB_BUFFER_POOL_READ_AHEAD"])
        physical_read = physical_read1 + physical_read2
        if logic_read == 0:
            logic_read = 1
        read_hit_rate = round((logic_read - physical_read) * 100 / logic_read, 2)
        real_status_info["INNODB_BUFFER_POOL_READ_PHYSICAL"] = physical_read
        real_status_info["INNODB_BUFFER_POOL_READ_HIT_RATE"] = read_hit_rate
        return real_status_info

    def _show_status_info(self, status_info):
        info_header = """
|----------------------|---------Basic-Status--------------|------Tran-Status------|----------------Command-Status-----------------|------------------InnoDB-Rows------------------|-----InnoDB-DATA-------|-------InnoDB-BufferPoll-Read------|
|------------Time------|-------QPS-|-------TPS-|---Connect-|----COMMIT-|--ROLLBACK-|----SELECT-|----INSERT-|----UPDATE-|----DELETE-|------READ-|----INSERT-|----UPDATE-|----DELETE-|------READ-|-----WRITE-|-----Logic-|--Physical-|--Hit-Rate-|"""
        if self._loop_counter % 20 == 1:
            self.monitor_logger.info(info_header)
        info_body_temp = '| {:>20} | {:>9} | {:>9} | {:>9} | {:>9} | {:>9} | {:>9} | {:>9} | {:>9} | {:>9} | {:>9} | {:>9} | {:>9} | {:>9} | {:>9} | {:>9} | {:>9} | {:>9} | {:>9} |'
        info_body = info_body_temp.format(
            status_info["CHECK_TIME"].strftime("%m-%d %H:%M:%S")+".{:03d}".format(int(status_info["CHECK_TIME"].microsecond/1000)),
            status_info["QUESTIONS"],
            status_info["COM_COMMIT"] + status_info["COM_ROLLBACK"],
            status_info["THREADS_CONNECTED"],
            status_info["COM_COMMIT"],
            status_info["COM_ROLLBACK"],
            status_info["COM_SELECT"],
            status_info["COM_INSERT"],
            status_info["COM_UPDATE"],
            status_info["COM_DELETE"],
            status_info["INNODB_ROWS_READ"],
            status_info["INNODB_ROWS_INSERTED"],
            status_info["INNODB_ROWS_UPDATED"],
            status_info["INNODB_ROWS_DELETED"],
            status_info["INNODB_DATA_READS"],
            status_info["INNODB_DATA_WRITES"],
            status_info["INNODB_BUFFER_POOL_READ_REQUESTS"],
            status_info["INNODB_BUFFER_POOL_READ_PHYSICAL"],
            "{:.2f}".format(status_info["INNODB_BUFFER_POOL_READ_HIT_RATE"])
        )
        self.monitor_logger.info(info_body)

    def start_monitor(self):
        logger.info("开始监控")
        self._check_server_conn()
        while self._loop_counter < self.check_count:
            status_info = self._get_real_status_info()
            if status_info is not None:
                self._show_status_info(status_info)
            time.sleep(self.check_interval)
            self._loop_counter += 1
        logger.info("完成监控")


def init_logger():
    LoggerHelper.init_logger(logger_level=logging.INFO)
    global logger
    logger = LoggerHelper.get_logger()


def main(mysql_host, mysql_port,
         mysql_user, mysql_pass,
         check_interval=1, check_count=10000):
    """
    监控MySQL活跃线程并将超过指定数量的活跃线程记录到日志中，方便DBA排查问题。
    :param mysql_host: MySQL实例地址
    :param mysql_port: MySQL实例端口
    :param mysql_user: MySQL实例账号
    :param mysql_pass: MySQL实例密码
    :param check_interval: 检查间隔
    :param check_count: 检查次数
    :return:
    """
    pm = MySQLQpsMonitor(
        mysql_host=mysql_host, mysql_port=mysql_port,
        mysql_user=mysql_user, mysql_pass=mysql_pass,
        check_interval=check_interval, check_count=check_count
    )
    pm.start_monitor()


if __name__ == '__main__':
    init_logger()
    fire.Fire(main)
